/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Golf_Manager.sqlite;

import Golf_Manager.sqlite.sql.GroupSql;
import Golf_Manager.sqlite.sql.JoinMemberSql;
import Golf_Manager.vo.Group;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import Golf_Manager.vo.JoinMember;
/**
 *
 * @author Administrator
 */
public class JoinMemberDAO {
    
    @SuppressWarnings("empty-statement")
    public static List<JoinMember> selectJoinMember(String selectKey, String orderString){
        List<JoinMember> JoinMembers = new ArrayList<>();
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
          
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:data.sqlite");
            pstmt = connection.prepareStatement(JoinMemberSql.getJoinMembersSql(selectKey, orderString).toString());
            rs = pstmt.executeQuery();
            
            while(rs.next()){
                if(selectKey != null && !selectKey.equals("")){
                    JoinMembers.add(new JoinMember(rs.getString("MEMBER_SEQ"), rs.getString("NAME"), rs.getString("BIRTH") ,rs.getString(selectKey)));
                } else {
                    JoinMembers.add(new JoinMember(rs.getString("MEMBER_SEQ"), rs.getString("NAME"), rs.getString("BIRTH")));
                }
            }
            
        } catch (SQLException | ClassNotFoundException e){
            Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            if ( rs != null ) try { rs.close(); } catch (SQLException e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
            if ( pstmt != null ) try { pstmt.close(); } catch (SQLException e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
            if ( connection != null ) try { connection.close(); } catch (SQLException e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
        }
        
        return JoinMembers;
    }
    
    @SuppressWarnings("empty-statement")
    public static Boolean saveGroupAndJoinMembers(String groupDate, String selectKey, String order, Boolean recordCheck, List<Group> groups, List<JoinMember> JoinMembers){
        
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Boolean success = false;
        
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:data.sqlite");
            connection.setAutoCommit(false);
            
            //기존 GROUP_MST 삭제 -조편성일자
            pstmt = connection.prepareStatement(GroupSql.getDeleteGroupMstSql(groupDate).toString());
            pstmt.executeUpdate();
            //신규 GROUP_MST 추가 -조편성일자
            pstmt = connection.prepareStatement(GroupSql.getInsterGroupMstSql(groupDate, selectKey, order, recordCheck).toString());
            pstmt.executeUpdate();
            //기존 GROUP_DTL 삭제 -조편성일자
            pstmt = connection.prepareStatement(GroupSql.getDeleteGroupDtlSql(groupDate).toString());
            pstmt.executeUpdate();
            //신규 GROUP_DTL 추가 -조편성일자
            for(Group group : groups){
                pstmt = connection.prepareStatement(GroupSql.getInsterGroupDtlSql(groupDate, group).toString());
                pstmt.executeUpdate();
            }
            //기존 JOINMEMBERS 삭제 -조편성일자
            pstmt = connection.prepareStatement(JoinMemberSql.getDeleteJoinMemberSql(groupDate).toString());
            pstmt.executeUpdate();
            //신규 JOINMEMBERS 추가 -조편성일자
            for(JoinMember joinMember : JoinMembers){
                pstmt = connection.prepareStatement(JoinMemberSql.getInsertJoinMemberSql(groupDate, joinMember).toString());
                pstmt.executeUpdate();
            }
            
            connection.commit();
                
            success = true;
          
        } catch (ClassNotFoundException | SQLException e){
            try { if ( connection != null ) connection.rollback(); } catch (Exception ex) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
        } finally {
            if ( rs != null ) try { rs.close(); } catch (Exception e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
            if ( pstmt != null ) try { pstmt.close(); } catch (Exception e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
            if ( connection != null ) try { connection.close(); } catch (Exception e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
        }
        
        return success;
    }
}
